#!/usr/bin/env node

/**
 * 优惠券表扩展迁移脚本
 * 为支持 iOS 复杂解锁条件而扩展后端数据库表结构
 * 
 * 执行方式: node scripts/migrate-coupon-schema.js
 * 
 * 注意: 此脚本幂等安全，可重复执行
 */

const { pool, testConnection } = require('../backend/src/config/database');

class CouponSchemaMigration {
  constructor() {
    this.migrations = [
      'addExtendedCouponFields',
      'createCouponUnlockConditionsTable',
      'createCouponTimePeriodsTable',
      'addIndexes',
      'insertDefaultConditionTypes'
    ];
  }

  async run() {
    console.log('🚀 开始优惠券表扩展迁移...\n');
    
    try {
      // 测试数据库连接
      const isConnected = await testConnection();
      if (!isConnected) {
        throw new Error('数据库连接失败');
      }

      // 执行所有迁移
      for (const migration of this.migrations) {
        await this[migration]();
      }

      console.log('\n✅ 优惠券表扩展迁移完成！');
      console.log('\n📊 迁移摘要:');
      console.log('   - 扩展 coupons 表字段 (iOS 兼容)');
      console.log('   - 创建 coupon_unlock_conditions 表');
      console.log('   - 创建 coupon_time_periods 表');
      console.log('   - 添加性能优化索引');
      console.log('   - 插入默认解锁条件类型');

    } catch (error) {
      console.error('❌ 迁移失败:', error.message);
      process.exit(1);
    }
  }

  /**
   * 添加优惠券表扩展字段
   */
  async addExtendedCouponFields() {
    console.log('📋 添加优惠券表扩展字段...');
    
    const alterSql = `
      ALTER TABLE coupons 
      ADD COLUMN IF NOT EXISTS unlock_condition_type ENUM(
        'registration_completion',
        'streak_achievement',
        'weekend_special',
        'time_based',
        'category_purchase',
        'mystery_reward',
        'location_based',
        'birthday_special',
        'engagement_reward',
        'profile_completion',
        'cash_voucher',
        'challenge_completion',
        'always_available'
      ) DEFAULT 'always_available' COMMENT 'iOS 解锁条件类型',
      
      ADD COLUMN IF NOT EXISTS unlock_condition_config JSON COMMENT 'iOS 解锁条件配置 (JSON 格式)',
      ADD COLUMN IF NOT EXISTS ios_coupon_id VARCHAR(50) UNIQUE COMMENT 'iOS 端优惠券 ID (对应 CouponConfig)',
      ADD COLUMN IF NOT EXISTS required_points INT DEFAULT 0 COMMENT '所需积分',
      ADD COLUMN IF NOT EXISTS required_membership_level ENUM('bronze', 'silver', 'gold', 'platinum', 'diamond') DEFAULT 'bronze' COMMENT '所需会员等级',
      ADD COLUMN IF NOT EXISTS required_order_count INT DEFAULT 0 COMMENT '所需订单数量',
      ADD COLUMN IF NOT EXISTS required_total_spent DECIMAL(10,2) DEFAULT 0.00 COMMENT '所需总消费金额',
      ADD COLUMN IF NOT EXISTS category_restrictions JSON COMMENT '适用品类限制',
      ADD COLUMN IF NOT EXISTS location_restrictions JSON COMMENT '地理位置限制',
      ADD COLUMN IF NOT EXISTS auto_distribute BOOLEAN DEFAULT FALSE COMMENT '自动发放给符合条件用户',
      ADD COLUMN IF NOT EXISTS display_order INT DEFAULT 0 COMMENT '显示顺序',
      ADD COLUMN IF NOT EXISTS background_color VARCHAR(7) DEFAULT '#FF6B35' COMMENT 'UI 背景色',
      ADD COLUMN IF NOT EXISTS text_color VARCHAR(7) DEFAULT '#FFFFFF' COMMENT 'UI 文字色',
      ADD COLUMN IF NOT EXISTS icon_name VARCHAR(50) COMMENT 'iOS 图标名称'
    `;

    try {
      await pool.execute(alterSql);
      console.log('   ✓ 优惠券表字段扩展成功');
    } catch (error) {
      if (error.code === 'ER_DUP_FIELDNAME') {
        console.log('   → 扩展字段已存在，跳过');
      } else {
        throw error;
      }
    }
  }

  /**
   * 创建优惠券解锁条件表
   */
  async createCouponUnlockConditionsTable() {
    console.log('📋 创建优惠券解锁条件表...');
    
    const createSql = `
      CREATE TABLE IF NOT EXISTS coupon_unlock_conditions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        coupon_id INT NOT NULL,
        condition_type ENUM(
          'min_order_amount',
          'category_purchase',
          'time_range',
          'day_of_week',
          'user_registration_days',
          'consecutive_orders',
          'birthday_month',
          'location_check',
          'item_quantity',
          'membership_level'
        ) NOT NULL COMMENT '条件类型',
        condition_value JSON NOT NULL COMMENT '条件值配置',
        is_required BOOLEAN DEFAULT TRUE COMMENT '是否必需满足',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
        INDEX idx_coupon_conditions (coupon_id, condition_type),
        INDEX idx_condition_type (condition_type)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      COMMENT='优惠券解锁条件表 - 支持复杂条件组合'
    `;

    try {
      await pool.execute(createSql);
      console.log('   ✓ 优惠券解锁条件表创建成功');
    } catch (error) {
      if (error.code === 'ER_TABLE_EXISTS_ERROR') {
        console.log('   → 解锁条件表已存在，跳过');
      } else {
        throw error;
      }
    }
  }

  /**
   * 创建优惠券时间段表
   */
  async createCouponTimePeriodsTable() {
    console.log('📋 创建优惠券时间段表...');
    
    const createSql = `
      CREATE TABLE IF NOT EXISTS coupon_time_periods (
        id INT AUTO_INCREMENT PRIMARY KEY,
        coupon_id INT NOT NULL,
        day_of_week TINYINT NOT NULL COMMENT '星期几 (0=周日, 6=周六)',
        start_time TIME NOT NULL COMMENT '开始时间',
        end_time TIME NOT NULL COMMENT '结束时间',
        timezone VARCHAR(50) DEFAULT 'Asia/Singapore' COMMENT '时区',
        is_active BOOLEAN DEFAULT TRUE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
        
        FOREIGN KEY (coupon_id) REFERENCES coupons(id) ON DELETE CASCADE,
        INDEX idx_coupon_time (coupon_id, day_of_week, start_time, end_time),
        INDEX idx_day_time (day_of_week, start_time, end_time)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
      COMMENT='优惠券生效时间段表 - 支持按星期和时间限制'
    `;

    try {
      await pool.execute(createSql);
      console.log('   ✓ 优惠券时间段表创建成功');
    } catch (error) {
      if (error.code === 'ER_TABLE_EXISTS_ERROR') {
        console.log('   → 时间段表已存在，跳过');
      } else {
        throw error;
      }
    }
  }

  /**
   * 添加性能优化索引
   */
  async addIndexes() {
    console.log('📋 添加性能优化索引...');

    // 通用添加索引的帮助函数，避免使用 IF NOT EXISTS
    const addIndex = async (table, indexName, columns) => {
      try {
        await pool.execute(`ALTER TABLE ${table} ADD INDEX ${indexName} (${columns})`);
      } catch (error) {
        if (error.code === 'ER_DUP_KEYNAME') {
          // 索引已存在，跳过
          return;
        }
        // 如果列不存在，保留错误给上层处理
        throw error;
      }
    };

    // coupons 表固定索引
    const couponIndexes = [
      { name: 'idx_coupons_ios_id', cols: 'ios_coupon_id' },
      { name: 'idx_coupons_unlock_type', cols: 'unlock_condition_type' },
      { name: 'idx_coupons_auto_distribute', cols: 'auto_distribute, is_active' },
      { name: 'idx_coupons_membership_level', cols: 'required_membership_level' },
      { name: 'idx_coupons_display_order', cols: 'display_order, is_active' }
    ];

    for (const idx of couponIndexes) {
      await addIndex('coupons', idx.name, idx.cols);
    }

    // user_coupons: 根据实际存在的列动态创建索引
    try {
      const [rows] = await pool.execute(`
        SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_coupons'
      `);
      const cols = new Set(rows.map(r => r.COLUMN_NAME));

      // 至少为 user_id 建立索引
      await addIndex('user_coupons', 'idx_user_coupons_user', 'user_id');

      // 优先选择 (user_id, is_used, created_at)
      if (cols.has('is_used') && cols.has('created_at')) {
        await addIndex('user_coupons', 'idx_user_coupons_compound', 'user_id, is_used, created_at');
      } else if (cols.has('is_used') && cols.has('obtained_at')) {
        await addIndex('user_coupons', 'idx_user_coupons_used_obtained', 'user_id, is_used, obtained_at');
      } else if (cols.has('obtained_at')) {
        await addIndex('user_coupons', 'idx_user_coupons_obtained', 'user_id, obtained_at');
      }
    } catch (error) {
      // 如果 user_coupons 不存在或列不匹配，给出提醒但不中断整个迁移
      console.warn('   ⚠ 创建 user_coupons 索引时出现警告:', error.message);
    }

    console.log('   ✓ 性能优化索引添加成功');
  }

  /**
   * 插入默认解锁条件类型数据
   */
  async insertDefaultConditionTypes() {
    console.log('📋 插入默认解锁条件配置...');
    
    // 检查是否已有 iOS 优惠券数据
    const [existing] = await pool.execute(
      'SELECT COUNT(*) as count FROM coupons WHERE ios_coupon_id IS NOT NULL'
    );
    
    if (existing[0].count > 0) {
      console.log('   → iOS 优惠券数据已存在，跳过初始化');
      return;
    }

    // 更新现有优惠券的默认配置
    const updateSql = `
      UPDATE coupons 
      SET 
        unlock_condition_type = 'always_available',
        unlock_condition_config = '{"description": "无限制条件", "auto_unlock": true}',
        required_membership_level = 'bronze',
        auto_distribute = FALSE,
        display_order = 999,
        background_color = '#FF6B35',
        text_color = '#FFFFFF'
      WHERE unlock_condition_type IS NULL OR unlock_condition_type = ''
    `;

    try {
      const [result] = await pool.execute(updateSql);
      console.log(`   ✓ 更新 ${result.affectedRows} 个现有优惠券的默认配置`);
    } catch (error) {
      console.warn('   ⚠ 更新现有优惠券配置时出现警告:', error.message);
    }
  }
}

// 执行迁移
async function main() {
  const migration = new CouponSchemaMigration();
  
  try {
    await migration.run();
  } catch (error) {
    console.error('迁移执行失败:', error);
    process.exit(1);
  } finally {
    // 关闭数据库连接
    try {
      await pool.end();
    } catch (error) {
      console.warn('关闭数据库连接时出现警告:', error.message);
    }
  }
}

// 如果直接执行此脚本
if (require.main === module) {
  main();
}

module.exports = CouponSchemaMigration;